﻿using System;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for AccountsDAL
/// </summary>
public class AccountsDAL
{

	dbConnection db =new dbConnection();

    public AccountsDAL()
    {
        //
        // TODO: Add constructor logic here
        //
    }


    public virtual int CreateAccount(AccountsBO Account)
    {
        SqlConnection cn = null;
        SqlCommand cmd = null;
        int Counter = 0;

        try
        {
            cn = new SqlConnection();
            cn.ConnectionString = db.GetCS();

            cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "INSERT INTO [Accounts] ( Amount, BranchId,AccCreatedDate) VALUES ( @Amount, @BranchId,@AccCreatedDate)";
            cmd.Parameters.AddWithValue("@Amount", Account.Amount);
            cmd.Parameters.AddWithValue("@BranchId", Account.BranchId);
            cmd.Parameters.AddWithValue("@AccCreatedDate", Account.AccCrtdDt);


            cn.Open();
            Counter = cmd.ExecuteNonQuery();

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            if (cn != null)
            {
				
				cn.Close();
                cn.Dispose();
               
            }
        }
        return (Counter);
    }

    public  int UpdateAccount(AccountsBO Account)
    {
        SqlConnection cn = null;
        SqlCommand cmd = null;
        int Counter = 0;


        try
        {
            cn = new SqlConnection();
            cn.ConnectionString = db.GetCS();

            cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "UPDATE [ACCOUNTS] SET  [Amount]=@Amount, [BranchId]=@BranchId,[AccCreatedDate]=@AccCreatedDate WHERE AccountId=@AccountId";

            //cmd.Parameters.AddWithValue("@CategoryId", CategoryId);
            cmd.Parameters.AddWithValue("@AccountId", Account.AccountId);
            cmd.Parameters.AddWithValue("@Amount", Account.Amount);
            cmd.Parameters.AddWithValue("@BranchId", Account.BranchId);
            cmd.Parameters.AddWithValue("@AccCreatedDate", Account.AccCrtdDt);


            cn.Open();
            Counter = cmd.ExecuteNonQuery();

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            if (cn != null)
            {
                cn.Close();
                cn.Dispose();
                cn = null;
            }
        }
        return (Counter);
    }
	public int DeleteCustomer(AccountsBO Account)
		{
		SqlConnection cn = null;
		SqlCommand cmd = null;
		int Counter = 0;


		try
			{
			cn = new SqlConnection();
			cn.ConnectionString = db.GetCS();

			cmd = new SqlCommand();
			cmd.Connection = cn;
			cmd.CommandType = CommandType.Text;
			cmd.CommandText = "Declare @counter as integer Select @counter=count(*) from Customers where accountid=@ACCOUNTID if @counter=1 begin DELETE FROM [Customers] WHERE ACCOUNTID=@ACCOUNTID end";

			cmd.Parameters.AddWithValue("@AccountId", Account.AccountId);

			cn.Open();
			Counter = cmd.ExecuteNonQuery();

			}
		catch (Exception ex)
			{
			throw new Exception(ex.Message);
			}
		finally
			{
			if (cn != null)
				{
				cn.Close();
				cn.Dispose();
				cn = null;
				}
			}
		return (Counter);
		}
    public  int DeleteRegistration(AccountsBO Account)
    {
        SqlConnection cn = null;
        SqlCommand cmd = null;
        int Counter = 0;


        try
        {
            cn = new SqlConnection();
            cn.ConnectionString = db.GetCS();

            cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Declare @counter as integer Select @counter=count(*) from Registrations where accountid=@ACCOUNTID if @counter=1 begin DELETE FROM [Registrations] WHERE ACCOUNTID=@ACCOUNTID end";
 
            cmd.Parameters.AddWithValue("@AccountId", Account.AccountId);

            cn.Open();
            Counter = cmd.ExecuteNonQuery();

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            if (cn != null)
            {
                cn.Close();
                cn.Dispose();
                cn = null;
            }
        }
        return (Counter);
    }

    public  int DeleteAccount(AccountsBO Account)
    {
        SqlConnection cn = null;
        SqlCommand cmd = null;
        int Counter = 0;


        try
        {
            cn = new SqlConnection();
            cn.ConnectionString = db.GetCS();

            cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "DELETE FROM [ACCOUNTS] WHERE ACCOUNTID=@ACCOUNTID";

            //cmd.Parameters.AddWithValue("@CategoryId", CategoryId);
            cmd.Parameters.AddWithValue("@AccountId", Account.AccountId);

            cn.Open();
            Counter = cmd.ExecuteNonQuery();
		

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            if (cn != null)
            {
                cn.Close();
                cn.Dispose();
                cn = null;
            }
        }
        return (Counter);
    }

    public  DataTable BranchFillDropDown()
    {
        SqlConnection cn = null;
        SqlCommand cmd = null;
        DataSet dSet1 = null;
        SqlDataAdapter sda = null;

        try
        {
            cn = new SqlConnection();
            cn.ConnectionString = db.GetCS();
            cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Select BranchId,BranchName from [Branches]";
            dSet1 = new DataSet();
            sda = new SqlDataAdapter(cmd);

            sda.Fill(dSet1, "Branches");

            return dSet1.Tables["Branches"];

        }

        catch
        {

            throw;

        }

        finally
        {

            dSet1.Dispose();

            sda.Dispose();

            cn.Close();

            cn.Dispose();

        }
    }

    

    public  DataTable Load()
    {

        SqlConnection cn = null;
        SqlCommand cmd = null;
        DataSet dSet = null;
        SqlDataAdapter sda = null;

        try
        {
            cn = new SqlConnection();
            cn.ConnectionString = db.GetCS();
            cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Select Accounts.AccountId,Accounts.Amount,Branches.BranchName,Accounts.AccCreatedDate from [Accounts],[Branches] where Accounts.BranchId=Branches.BranchId";
            dSet = new DataSet();
            sda = new SqlDataAdapter(cmd);
           
            sda.Fill(dSet, "Accounts");
           
            foreach (DataRow myRow in dSet.Tables["Accounts"].Rows)
            {
                if (myRow.HasErrors) Console.WriteLine(myRow[0] + "\n" + myRow.RowError);
            }

            return dSet.Tables["Accounts"];

        }

        catch
        {

            throw;

        }

        finally
        {

            dSet.Dispose();

            sda.Dispose();

            cn.Close();

            cn.Dispose();

        }
    }
}



